package cn.ctbu.xiaopeng.studentscoremanage.dao;


import cn.ctbu.xiaopeng.studentscoremanage.pojo.dto.ScoreQueryDTO;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.entity.SC;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.ScorePageVO;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.page.ScorePageResult;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.page.TotalRow;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author xiaopeng
 * @version 1.0
 */
public interface SCRepository extends JpaRepository<SC, Integer> {

    @Query("SELECT new cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.page.TotalRow(" +
            "SUM(sc.score), " +      // 计算总分
            "AVG(sc.gpa)) " +        // 计算平均绩点
            "FROM tb_sc sc")
    TotalRow getTotalRow();

    /**
     * 根据条件查询 SC 表中的数据
     * @param scoreQueryDTO
     * @param pageable
     * @return
     */
    @Query("SELECT new cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.ScorePageVO(" +
            "sc.id, s.sno, s.name, c.courseNo, s.sex, c.cname, t.tno,t.name, c.assessMethod" +
            " ,sc.score, sc.gpa, " +  // 选择返回字段
            "cl.className, c.semester,dep.department, c.credits, c.hours) " +
            "FROM tb_sc sc " +
            "JOIN tb_student s ON sc.sno = s.sno " +  // 连接学生表
            "JOIN tb_course c ON sc.courseNo = c.courseNo " +  // 连接课程表
            "JOIN tb_teacher t ON c.tno = t.tno " +  // 连接教师表,该课程的任课老师
            "JOIN tb_class cl ON s.classNo = cl.classNo " +  // 连接班级表
            "JOIN tb_department dep ON s.departmentId = dep.departmentId " +  // 连接部门表
            "WHERE (:#{#scoreQueryDTO.stuName} IS NULL OR s.name LIKE %:#{#scoreQueryDTO.stuName}%) " +
            "AND (:#{#scoreQueryDTO.cname} IS NULL OR c.cname LIKE %:#{#scoreQueryDTO.cname}%) " +
            "AND (:#{#scoreQueryDTO.tname} IS NULL OR t.name LIKE %:#{#scoreQueryDTO.tname}%) " +
            "AND (:#{#scoreQueryDTO.semester} IS NULL OR c.semester LIKE %:#{#scoreQueryDTO.semester}%) " +
            "AND (:#{#scoreQueryDTO.department} IS NULL OR dep.department LIKE %:#{#scoreQueryDTO.department}%) " +
            "AND (:#{#scoreQueryDTO.credits} IS NULL OR c.credits = :#{#scoreQueryDTO.credits}) " +
            "AND (:#{#scoreQueryDTO.hours} IS NULL OR c.hours = :#{#scoreQueryDTO.hours}) " +
            "AND (:#{#scoreQueryDTO.score} IS NULL OR sc.score = :#{#scoreQueryDTO.score}) " +
            "AND (:#{#scoreQueryDTO.gpa} IS NULL OR sc.gpa = :#{#scoreQueryDTO.gpa}) " +
            "AND (:#{#scoreQueryDTO.assessMethod} IS NULL OR c.assessMethod LIKE %:#{#scoreQueryDTO.assessMethod}%)")
    Page<ScorePageVO> getScorePageByConditions(@Param("scoreQueryDTO") ScoreQueryDTO scoreQueryDTO, Pageable pageable);


    /**
     * 根据条件查询 SC 表中的所有数据
     * @param scoreQueryDTO
     * @return
     */
    @Query("SELECT new cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.ScorePageVO(" +
            "sc.id, s.sno, s.name, c.courseNo, s.sex, c.cname, t.tno,t.name, c.assessMethod" +
            " ,sc.score, sc.gpa, " +  // 选择返回字段
            "cl.className, c.semester,dep.department, c.credits, c.hours) " +
            "FROM tb_sc sc " +
            "JOIN tb_student s ON sc.sno = s.sno " +  // 连接学生表
            "JOIN tb_course c ON sc.courseNo = c.courseNo " +  // 连接课程表
            "JOIN tb_teacher t ON c.tno = t.tno " +  // 连接教师表,该课程的任课老师
            "JOIN tb_class cl ON s.classNo = cl.classNo " +  // 连接班级表
            "JOIN tb_department dep ON s.departmentId = dep.departmentId " +  // 连接部门表
            "WHERE (:#{#scoreQueryDTO.stuName} IS NULL OR s.name LIKE %:#{#scoreQueryDTO.stuName}%) " +
            "AND (:#{#scoreQueryDTO.cname} IS NULL OR c.cname LIKE %:#{#scoreQueryDTO.cname}%) " +
            "AND (:#{#scoreQueryDTO.tname} IS NULL OR t.name LIKE %:#{#scoreQueryDTO.tname}%) " +
            "AND (:#{#scoreQueryDTO.semester} IS NULL OR c.semester LIKE %:#{#scoreQueryDTO.semester}%) " +
            "AND (:#{#scoreQueryDTO.department} IS NULL OR dep.department LIKE %:#{#scoreQueryDTO.department}%) " +
            "AND (:#{#scoreQueryDTO.credits} IS NULL OR c.credits = :#{#scoreQueryDTO.credits}) " +
            "AND (:#{#scoreQueryDTO.hours} IS NULL OR c.hours = :#{#scoreQueryDTO.hours}) " +
            "AND (:#{#scoreQueryDTO.score} IS NULL OR sc.score = :#{#scoreQueryDTO.score}) " +
            "AND (:#{#scoreQueryDTO.gpa} IS NULL OR sc.gpa = :#{#scoreQueryDTO.gpa}) " +
            "AND (:#{#scoreQueryDTO.assessMethod} IS NULL OR c.assessMethod LIKE %:#{#scoreQueryDTO.assessMethod}%)")
    List<ScorePageVO> getAllSCPByConditions(@Param("scoreQueryDTO") ScoreQueryDTO scoreQueryDTO);
    /**
     * 根据id批量删除
     * @param ids
     */
    @Modifying
    @Query("DELETE FROM tb_sc sc WHERE sc.id IN :ids")
    void deleteByIds(List<Integer> ids);



/*    *//**
     * 查询指定 cno 在 SC 表中出现的次数
     * @param cno 课程编号
     * @return 指定 cno 出现的次数
     *//*
    @Query("SELECT COUNT(score) FROM score score WHERE score.courseNo = :cno")
    Long countByCno(@Param("cno") String cno);*/

}
